package openc.model;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Vector;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.ParserConfigurationException;
import org.jdom.input.DOMBuilder;
import org.jdom.Document;
import org.jdom.Element;
import org.xml.sax.SAXException;

public class CatalogueManager {
    public CatalogueManager() {
        initSQLConnection();
        initXML();
    }

    public HashMap<String, String> getCatalogues() {
        HashMap<String, String> result = new HashMap<String, String>();
            for (Object childobj : doc.getRootElement().getChildren("catalogue")) {
                Element child = (Element) childobj;
                result.put(child.getAttributeValue("table"), child.getAttributeValue("name"));
            }
        
        return result;
    }

    public Vector<Vector<Object>> getElements(String catalogue) {
        Vector<Vector<Object>> result = new Vector<Vector<Object>>();

        try {         
            String query = "SELECT ID, NAME FROM " + catalogue;
            Statement st = cn.createStatement();
            ResultSet rs = st.executeQuery(query);

            while (rs.next()) {
                Vector<Object> row = new Vector<Object>();
                row.add(rs.getInt("ID"));
                row.add(rs.getString("NAME"));
                result.add(row);
            }
        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            System.err.println("SQLState: " + ex.getSQLState());
            System.err.println("VendorError: " + ex.getErrorCode());
        }

        return result;
    }

    public HashMap<String, Object> getElement(String catalogue, int id) {
        HashMap<String, Object> result = new HashMap();
        try {
            HashMap<String, String> atttributes = getAttributes(catalogue);
            String query = "SELECT ";
            Iterator<String> it = atttributes.keySet().iterator();
            while (it.hasNext()) {
                query = query + it.next();
                if (it.hasNext())
                    query = query + ", ";
            }
            query = query + " FROM " + catalogue + " WHERE ID = ?";
            System.out.println(query);
            PreparedStatement st = cn.prepareStatement(query);
            st.setInt(1, id);
            ResultSet rs = st.executeQuery();

            it = atttributes.keySet().iterator();
            while (rs.next()) {
                while (it.hasNext()) {
                    String attrName = it.next();
                    result.put(attrName, rs.getObject(attrName));
                }
            }
        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            System.err.println("SQLState: " + ex.getSQLState());
            System.err.println("VendorError: " + ex.getErrorCode());
        }
        return result;
    }

    public HashMap<String, String> getAttributes(String catalogue) {
        HashMap<String, String> result = new HashMap<String, String>(2);
        result.put("ID", "Integer");
        result.put("Name", "String");

        for (Object childobj : doc.getRootElement().getChildren("catalogue")) {
            Element child = (Element) childobj;
            if (child.getAttributeValue("table").equals(catalogue))
                for (Object attrоbj : child.getChildren("attribute"))
                {
                    Element attr = (Element) attrоbj;
                    result.put(attr.getAttributeValue("name"), attr.getAttributeValue("type"));
                }
        }

        return result;
    }

    private void initSQLConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            cn = DriverManager.getConnection("jdbc:mysql://localhost:3306/d17",
                                                                "root", "root");
        } catch (ClassNotFoundException ex) {
            System.err.println(ex.getMessage());
        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            System.err.println("SQLState: " + ex.getSQLState());
            System.err.println("VendorError: " + ex.getErrorCode());
        }
    }

    private void initXML() {
         try {
            File xmlfile = new File("c:/openc.xml");
            DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
            DocumentBuilder db = dbf.newDocumentBuilder();
            org.w3c.dom.Document domDocument = db.parse(xmlfile);

            DOMBuilder builder = new DOMBuilder();
            doc = builder.build(domDocument);
        } catch (IOException ex) {
            System.err.println(ex.getMessage());
        } catch (ParserConfigurationException ex) {
            System.err.println(ex.getMessage());
        } catch (SAXException ex) {
            System.err.println(ex.getMessage());
        }
    }

    private Connection cn;
    private Document doc;
}